package net.hn.hnms.biz.energy.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import net.hn.hnms.biz.energy.domain.PowerAnalysis;
import net.hn.hnms.biz.energy.vo.PowerStatisticsInfoVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface PowerAnalysisMapper  extends BaseMapper<PowerAnalysis> {


    @Select("<script>" +
            " \n" +
            "select a.time as years, \n" +
            "sum((b.one-a.one)+(d.one-c.one)) as trough,\n" +
            "\n" +
            " sum(b.one-a.one) as trough_one_five, "+
            " sum(d.one-c.one) as trough_eleven_seventeen, "+
            "sum(b.one) \"04:55\",\n" +
            "sum(a.one) \"00:55\",\n" +
            "\n" +
            "sum(d.one) \"16:55\",\n" +
            "sum(c.one) \"10:55\",\n" +
            "\n" +
            "\n" +
            "sum(e.one) \"23:55\",\n" +
            "\n" +
            "sum(j.one) \"00:01\",\n" +
            "sum(h.one) \"06:55\",\n" +
            "sum(i.one) \"08:55\",\n" +
            "\n" +
            "sum(k.one) \"17:55\",\n" +
            "sum(d.one) \"16:55\",\n" +
            "\n" +
            "sum(m.one) \"07:00\",\n" +
            "sum(n.one) \"09:00\",\n" +
            "sum(o.one) \"18:00\",\n" +
            "\n" +
            " sum((a.one-j.one)+(h.one-b.one)+(c.one-i.one)+(k.one-d.one)) as average," +
            " sum(a.one-j.one) as average_zero_one, "+
            " sum(h.one-b.one) as average_five_seven, "+
            " sum(c.one-i.one) as average_nine_eleven, "+
            " sum(k.one-d.one) as average_seventeen_eighteen, "+
            " sum(i.one-h.one) as highest_seven_nine, "+
            " sum(e.one-k.one) as highest_eighteen_twefour, "+
            "\n" +
            "sum((i.one-h.one)+(e.one-k.one)) as highest " +
            "from (\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
//            "-- 谷\n" +
//            "--  1\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '00:55:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time  <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time) a \n" +
            "\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
//            "-- 谷\n" +
//            "--  1\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '04:55:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") b on a.time= b.time\n" +
            "\n" +
            "--------\n" +
            "\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
//            "-- 谷\n" +
//            "--  1\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '10:55:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") c on a.time= c.time\n" +
            "\n" +
            "\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
//            "-- 谷\n" +
//            "--  1\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '16:55:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") d on a.time= d.time\n" +
            "\n" +
            "\n" +
//            "-- 平\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '23:55:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") e on a.time= e.time\n" +
            "\n" +
            "\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '06:55:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") h on a.time= h.time\n" +
            "\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '08:55:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") i on a.time= i.time\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '17:55:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") k on a.time= k.time\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '00:01:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") j on a.time= j.time\n" +
            "\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '07:00:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") m on a.time= m.time\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '09:00:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") n on a.time= n.time\n" +
            "left join \n" +
            "\n" +
            "\n" +
            "(\n" +
            "\n" +
            "select a.time,\n" +
            "sum(b.point_value) as one \n" +
            "from \n" +
            "(SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)) a\n" +
            "left join (\n" +
            "select t.install_location,t.device_name,\n" +
            "sum(point_value) as point_value,to_char(t.data_time, 'yyyy-MM') as time,\n" +
            "to_char(data_time, 'yyyy-MM-dd')\n" +
            "from \n" +
            "(\n" +
            "select * , row_number() over(partition by install_location,device_name,to_char(data_time, 'yyyy-MM-dd') order by a.data_time,a.id  asc ) as row_id from \n" +
            "(\n" +
            "select  gns.id ,gns.data_time,gbd.install_location,gbd.device_name,gns.point_value,gbs.point_location from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_his_sensormonitor gns\n" +
            "  on gbs.point_code=gns.point_code\n" +
            "  INNER JOIN dwd_gdjk_base_deviceinfo gbd\n" +
            "  on  gbs.device_code=gbd.device_code\n" +
            "\t\t\t\t\t\t\twhere   gbs.point_type_code ='2110'  and (to_char(gns.data_time, 'hh24:mi:ss') <![CDATA[ >= ]]>  '18:00:00')\n" +
            "\t\t\t\t\t\t\tand gns.data_time <![CDATA[ > ]]>  '2022-12-10'\n" +
            " and (to_char(gns.data_time, 'yyyy-MM-dd') <![CDATA[ = ]]> #{yearOfMonth})  "+
            "\t\t\t\t\t\t\tand gbd.device_name not in('3#进线','4#进线')\n" +
//            "\t\t\t\t\t\t\t-- and( gbs.point_location like '%正向%' or gbs.point_location like '%反向%')\n" +
            "\t\t\t\t\t\t\t\torder by  gns.data_time, gns.id asc \n" +
            "\n" +
            ") a\n" +
            ") as t\n" +
            "where t.row_id <![CDATA[ <= ]]>  2 \n" +
            "group by t.install_location,t.device_name,to_char(data_time, 'yyyy-MM-dd'),to_char(data_time, 'yyyy-MM')\n" +
            ") b on a.time= b.time\n" +
            "group by a.time\n" +
            "\n" +
            "\n" +
            "\n" +
            ") o on a.time= o.time\n" +
            "group by a.time " +
            "</script>")
    List<PowerStatisticsInfoVo> calculationPower(@Param("year")Integer year , @Param("yearOfMonth")String yearOfMonth);





    @Select("<script>" +
            " select a.time as years," +
            "round(cast(COALESCE(b.trough,'0') as numeric)/10000,2) as trough,\n" +
            "round(cast(COALESCE(b.average,'0') as numeric)/10000,2) as average," +
            "round(cast(COALESCE(b.highest,'0') as numeric)/10000,2) as highest\n " +
            "from (\n" +
            "SELECT concat((\tselect string_agg(concat(#{year}, '-'), null)),lpad(generate_series :: text,2,'0')) as time from generate_series(1,12)\n" +
            ") a left join (\n" +
            "\n" +
            "SELECT years as time,trough,average,highest FROM power_analysis where years like  concat('%',#{year},'%')  order by years\n" +
            ") b on a.time = b.time\n " +
            "</script>")
    List<PowerStatisticsInfoVo> selectPowerConsumptionAnalysisByYear(@Param("year")String year, @Param("mineCode")String mineCode);






//    @Select("<script>" +
//            " select a.install_location,a.device_name,sum(a.you) as wugongdiandu,sum(a.wu) as yougongdiandu from ( " +
//            " select gbd.install_location,gbd.device_name,gbs.point_type_code,  " +
//            " case gbs.point_type_code when '2111' then sum(gns.point_value) else 0 end as you, " +
//            " case gbs.point_type_code when '2110' then sum(gns.point_value) else 0 end as wu " +
//            " from dwd_gdjk_base_sensorinfo gbs INNER JOIN dwd_gdjk_now_sensormonitor gns " +
//            "   on gbs.point_code=gns.point_code " +
//            "   INNER JOIN dwd_gdjk_base_deviceinfo gbd " +
//            "   on  gbs.device_code=gbd.device_code " +
//            " where   gbs.point_type_code in('2111','2110')  " +
//            " and to_char(gns.data_time,'yyyy-mm') = #{beginDate} " +
//
//            " <if test='mineCode != null and mineCode != \"\" '> "+
//            " and gns.mine_code = #{mineCode} and gbd.mine_code = #{mineCode} and gbs.mine_code=#{mineCode}  " +
//            " </if>"+
//            " <if test=' flag ==1'> "+
//            " and ((to_char(gns.data_time, 'hh24:mi:ss') between '01:00:00'  and '04:59:59') " +
//            " or ( to_char(gns.data_time, 'hh24:mi:ss') between '11:00:00' and '16:59:59')) " +
//            " </if>"+
//            " <if test=' flag ==2'> "+
//            "  and ( (to_char(gns.data_time, 'hh24:mi:ss') between '00:00:00' and '00:59:59') " +
//            "  or  (to_char(gns.data_time, 'hh24:mi:ss') between '05:00:00' and '06:59:59') " +
//            "  or  (to_char(gns.data_time, 'hh24:mi:ss') between '09:00:00' and '10:59:59') " +
//            "  or  (to_char(gns.data_time, 'hh24:mi:ss') between '17:00:00' and '17:59:59')) " +
//            " </if>"+
//            " <if test=' flag ==3'> "+
//            " and ((to_char(gns.data_time, 'hh24:mi:ss') between '07:00:00' and  '08:59:59') " +
//            " or ( to_char(gns.data_time, 'hh24:mi:ss') between '18:00:00' and  '23:59:59')) " +
//            " </if>"+
//            " group by gbd.install_location,gbd.device_name,gbs.point_type_code " +
//            " ) a group by a.install_location,a.device_name order by a.install_location,a.device_name " +
//            "</script>")


    @Select("<script>" +
            "SELECT years as time,\n" +
            " <if test=' flag ==1'> "+
            " round(cast(COALESCE(trough_one_five,'0') as numeric)/10000,2) as trough_one_five,\n" +
            " round(cast(COALESCE(trough_eleven_seventeen,'0') as numeric)/10000,2) as trough_eleven_seventeen\n" +
            " </if>"+
            " <if test=' flag ==2'> "+
            " round(cast(COALESCE(average_zero_one,'0') as numeric)/10000,2) as average_zero_one,\n" +
            " round(cast(COALESCE(average_five_seven,'0') as numeric)/10000,2) as average_five_seven,\n" +
            " round(cast(COALESCE(average_nine_eleven,'0') as numeric)/10000,2) as average_nine_eleven,\n" +
            " round(cast(COALESCE(average_seventeen_eighteen,'0') as numeric)/10000,2) as average_seventeen_eighteen " +
            " </if>"+
            " <if test=' flag ==3'> "+
            " round(cast(COALESCE(highest_seven_nine,'0') as numeric)/10000,2) as highest_seven_nine,\n" +
            " round(cast(COALESCE(highest_eighteen_twefour,'0') as numeric)/10000,2) as highest_eighteen_twefour " +
            " </if>"+
            " FROM power_analysis where years = #{beginDate}  " +
            "</script>")
    Page<PowerStatisticsInfoVo> selectPowerConsumptionAnalysisDetails(Page<Object> build, @Param("beginDate")String beginDate, @Param("flag")Integer flag, @Param("mineCode")String mineCode);



    @Select("<script>" +
            " SELECT years  as years,trough,average,highest,trough_one_five," +
            " trough_eleven_seventeen," +
            " average_zero_one," +
            " average_five_seven," +
            " average_nine_eleven," +
            " average_seventeen_eighteen," +
            " highest_seven_nine," +
            " highest_eighteen_twefour" +
            " FROM power_analysis where years = #{yearOfMonth} " +
            "</script>")
    PowerStatisticsInfoVo selectPowerConsumptionAnalysisByYearOfMonth( @Param("yearOfMonth")String yearOfMonth, String mineCode);


    @Update("<script>" +
            " update power_analysis " +
            " set trough = #{trough} " +
            " , average =#{average} " +
            " , highest = #{highest} " +
            " , trough_one_five = #{troughOneFive} " +
            " , trough_eleven_seventeen = #{troughElevenSeventeen} " +
            " , average_zero_one = #{averageZeroOne} " +
            " , average_five_seven = #{averageFiveSeven} " +
            " , average_nine_eleven = #{averageNineEleven} " +
            " , average_seventeen_eighteen = #{averageSeventeenEighteen} " +
            " , highest_seven_nine = #{highestSevenNine} " +
            " , highest_eighteen_twefour = #{highestEighteenTwefour} " +
            " where years = #{id} " +
            "</script>")
    int updatePowerAnalysisByIds(@Param("id") String id,@Param("trough") String trough,
                                 @Param("average") String average,
                                 @Param("highest") String highest,
                                 @Param("troughOneFive") String troughOneFive,
                                 @Param("troughElevenSeventeen") String troughElevenSeventeen,
                                 @Param("averageZeroOne") String averageZeroOne,
                                 @Param("averageFiveSeven") String averageFiveSeven,
                                 @Param("averageNineEleven") String averageNineEleven,
                                 @Param("averageSeventeenEighteen") String averageSeventeenEighteen,
                                 @Param("highestSevenNine") String highestSevenNine,
                                 @Param("highestEighteenTwefour") String highestEighteenTwefour
                                 );

}
